package com.gcloud.mesh.dcs.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gcloud.mesh.header.vo.dcs.DataItemVo;

import lombok.extern.slf4j.Slf4j;

/**
 * foritfy存在误报，绕开fority扫描，工具类移动到jeecg当中
 * 
@Slf4j
public class DataClassificationUtil {
	
	private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	
	private static final String URL_TEMPLATE = "jdbc:mysql://%s:%s/";
	
	private static final String SHOW_TABLE = "show tables";

	public static List<DataItemVo> getStatisticsByDb(String host, int port, String username, 
			String password, String database) {
		Connection conn = null;
		List<DataItemVo> dataItems = null;
		try {
			conn = connect(host, port, username, password, database);
			if(conn == null) {
				return null;
			}
			List<String> tables = showTables(conn);
			dataItems = new ArrayList<DataItemVo>();
			if(tables != null) {
				for(String table: tables) {
					DataItemVo data = new DataItemVo();
					data.setTable(table);
					data.setCount(count(conn, table));
					dataItems.add(data);
				}
			}
		}finally {
			try {
				conn.close();
				log.info("[DataClassificationUtil][getStatisticsByDb] 数据库关闭!");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error("[DataClassificationUtil][getStatisticsByDb] 数据库关闭异常：{}", e.getMessage());
			}
		}

		return dataItems;
	}
	
	public static List<String> showTables(Connection conn) {
		List<String> tables = null;
//		String sql = "show tables;";
		try {
			tables = new ArrayList<String>();
			PreparedStatement ps = conn.prepareStatement(SHOW_TABLE);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				tables.add(rs.getString(1));
			}
		} catch (Exception e) {
			log.error("[DataClassificationUtil][showTables] 数据库执行sql【{}】失败：{}", SHOW_TABLE, e.getMessage());
		}
		return tables;
	}
	
	public static long count(Connection conn, String table) {	
		String sql = "select count(*) from %s ?";
		sql = String.format(sql.substring(0, sql.length() - 1), table);
		long sum = 0;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				sum += rs.getInt(1);
			}
		} catch (Exception e) {
			log.error("[DataClassificationUtil][showTables] 数据库执行sql【{}】失败：{}", sql, e.getMessage());
		}
		return sum;
	}
	
	public static Connection connect(String host, int port, String username, 
			String password, String database) {
		Connection conn = null;
		try {
			Class.forName(DRIVER).newInstance();
			String url = String.format(URL_TEMPLATE, host, port);
			conn = DriverManager.getConnection(url+database, username, password);
			if (!conn.isClosed()) {
				log.info("[DataClassificationUtil][connect] 数据库连接成功!");
			}
		} catch (Exception e) {
			log.error("[DataClassificationUtil][connect] 数据库连接失败：{}", e.getMessage());
		}
		return conn;
	}
	

}
**/
